Opening Access queries through ODBC

ODBC gives you more control over which parts of a database you can use. For this reason, using an Access query through ODBC may require a few extra steps.

To open an Access query through ODBC

1. On the File menu, click Options.
2. Click the Database tab.

   

3. Select the Views and/or Reprompt user when connecting check box(es).
   • Selecting Views automatically displays any available queries in your Access database.
   • Selecting Reprompt user when connecting displays the Allow Reporting On dialog box every time you select an ODBC data source. This dialog box provides the same options as the Allow Reporting On section on the SQL tab of the Options dialog box, but provides those options for every ODBC data source you select.
4. In addition, you can specify Table name LIKE and Owner LIKE options, if you wish.
   • Table name LIKE is based on the SQL LIKE clause. This option allows you to specify the kinds of table names you want to appear in the Choose SQL Table dialog box. You can use the underscore character (_) or the percent sign character (%) as wildcards with this function. The underscore character specifies any single character, while the percent sign signifies any character string. For example, DAV_ matches DAVE only, while DAV% matches DAVE and DAVID. Table name LIKE C% displays only those tables that have a table name beginning with the letter C.
   • Owner LIKE is also based on the SQL LIKE clause. Owner LIKE allows you to select the Owner (or Creator or Alias) of the table, not the table name itself. For example Owner LIKE C% displays only those tables that have an owner beginning with the letter C.
5. Click OK to exit the Options dialog box.

6. Create a new report and choose ODBC as the data source for your Access database.

   If you did not specify a particular Access database file with your Access ODBC data source, the Select Database dialog box appears.

   Note:    If your database requires a user name and password, or any other log on information, a log on dialog box appears.

7. Locate and select the database that contains the Access query you want to use and then expand it.

   If you selected the Reprompt user when connecting check box in the Options dialog box, the Allow Reporting On dialog box appears. Otherwise, skip to Step 9.

   

8. Make sure the Views check box is selected and click OK when finished.
9. Highlight your query, click Add and then Close.

   The Design tab appears along with the Field Explorer dialog box. Your Access query, and all fields associated with that query, appear under Database Fields.

Note:    You cannot use Access Action queries or Update queries in Seagate Crystal Reports. However, you can use Access Select queries and Cross-tab queries.